package management;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import com.toedter.calendar.JDateChooser;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Date;

public class Query {

    private Connection connection;
    private JFrame frame;
    private JPanel inputPanel;
    private JLabel labelStartDate;
    private JDateChooser startDateChooser;
    private JLabel labelEndDate;
    private JDateChooser endDateChooser;
    private JLabel labelCategory;
    private JLabel labelItem;
    private JTextField itemTextField;
    private JButton queryButton;
    private JTable table;
    private JLabel totalLabel;
    private JScrollPane scrollPane;

    public void QFace() {
        frame = new JFrame("消费信息查询");
//        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setSize(800, 600);
        frame.setLayout(new BorderLayout());

        // 创建输入面板，使用GridLayout管理输入字段
        inputPanel = new JPanel(new GridLayout(4, 2, 10, 10));

        labelStartDate = new JLabel("开始日期：");
        startDateChooser = new JDateChooser();

        labelEndDate = new JLabel("结束日期：");
        endDateChooser = new JDateChooser();

        labelCategory = new JLabel("选择消费类别：");
        // 从数据库获取消费类别列表并创建JComboBox
        List<String> categories = getCategoriesFromDatabase();
        JComboBox<String> categoryComboBox = new JComboBox<>(categories.toArray(new String[0]));

        labelItem = new JLabel("输入消费项目关键字：");
        itemTextField = new JTextField(20);

        // 添加组件到输入Panel
        inputPanel.add(labelStartDate);
        inputPanel.add(startDateChooser);
        inputPanel.add(labelEndDate);
        inputPanel.add(endDateChooser);
        inputPanel.add(labelCategory);
        inputPanel.add(categoryComboBox);
        inputPanel.add(labelItem);
        inputPanel.add(itemTextField);

        queryButton = new JButton("查询");

        queryButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                Querycustomer();
            }
        });

        // 创建按钮面板，使用FlowLayout将按钮右对齐，这里先创建但暂不添加到界面，后面统一添加
        JPanel buttonPanel = new JPanel(new FlowLayout(FlowLayout.RIGHT));
        buttonPanel.add(queryButton);

        // 创建用于放置表格的滚动面板，初始先不添加内容
        scrollPane = new JScrollPane();

        // 创建总计金额标签，初始先不添加内容
        totalLabel = new JLabel();

        // 将输入面板、按钮面板、滚动面板按顺序添加到主窗口的中间区域（BorderLayout.CENTER），实现上下布局
        JPanel centerPanel = new JPanel(new BorderLayout());
        centerPanel.add(inputPanel, BorderLayout.NORTH);
        centerPanel.add(scrollPane, BorderLayout.CENTER);
        centerPanel.add(totalLabel, BorderLayout.SOUTH);

        frame.add(centerPanel, BorderLayout.CENTER);
        frame.add(buttonPanel, BorderLayout.SOUTH);

        // 显示框架
        frame.setLocation(500, 200);
        frame.setVisible(true);
    }

    // 获取数据库连接的方法
    private Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.err.println("加载驱动错误：" + e.getMessage());
            return null;
        }
        String username = "root";
        String password = "root";
        String url = "jdbc:mysql://localhost:3306/project";
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.err.println("数据库连接错误：" + throwables.getMessage());
            return null;
        }
    }

    // 从consumption_category表中获取category_name字段作为消费类别列表的方法
    private List<String> getCategoriesFromDatabase() {
        List<String> categoryList = new ArrayList<>();
        String sql = "SELECT category_name FROM consumption_category";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                categoryList.add(resultSet.getString("category_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("从数据库获取消费类别时出错: " + e.getMessage());
            JOptionPane.showMessageDialog(null, "获取消费类别数据失败，请检查数据库配置或表结构！", "错误", JOptionPane.ERROR_MESSAGE);
        }
        return categoryList;
    }

    public void Querycustomer() {
        // 获取数据库连接
        connection = getConnection();
        if (connection == null) {
            System.err.println("无法获取数据库连接");
            return;
        }

        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM consumer WHERE consumer_id =? AND 1=1 ");
        // 使用正确的数据库字段名consumer_id来基于用户进行筛选，添加基于用户ID的筛选条件，方便后续拼接其他条件

        List<Object> parameters = new ArrayList<>();
        // 从Management类获取用户ID并添加到参数列表中
        parameters.add(Management.getID());

        // 处理日期范围条件
        Date startDateUtil = startDateChooser.getDate();
        Date endDateUtil = endDateChooser.getDate();
        if (startDateUtil!= null && endDateUtil!= null) {
            sqlBuilder.append(" AND date_time BETWEEN? AND? ");
            java.sql.Date startDateSql = new java.sql.Date(startDateUtil.getTime());
            java.sql.Date endDateSql = new java.sql.Date(endDateUtil.getTime());
            parameters.add(startDateSql);
            parameters.add(endDateSql);
        }

        // 处理类别条件
        JComboBox<String> categoryComboBox = (JComboBox<String>) inputPanel.getComponent(5);
        String selectedCategory = (String) categoryComboBox.getSelectedItem();
        if (selectedCategory!= null &&!selectedCategory.isEmpty()) {
            sqlBuilder.append(" AND category =? ");
            parameters.add(selectedCategory);
        }

        // 处理关键字条件
        String keyword = itemTextField.getText();
        if (keyword!= null &&!keyword.isEmpty()) {
            sqlBuilder.append(" AND consumption_project LIKE? ");
            parameters.add("%" + keyword + "%");
        }

        String sql = sqlBuilder.toString();

        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            // 设置参数，从索引1开始，因为索引0已经设置为用户ID（这里是consumer_id）了
            for (int i = 0; i < parameters.size(); i++) {
                preparedStatement.setObject(i + 1, parameters.get(i));
            }

            // 执行查询并处理结果
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                // 创建表格模型
                DefaultTableModel tableModel = new DefaultTableModel();
                tableModel.addColumn("消费编号");
                tableModel.addColumn("日期");
                tableModel.addColumn("类别");
                tableModel.addColumn("项目");
                tableModel.addColumn("金额");
                double totalAmount = 0;

                // 用于临时存储查询结果的列表
                List<Object[]> resultList = new ArrayList<>();

                while (resultSet.next()) {
                    double amount = resultSet.getDouble("number");
                    totalAmount += amount;
                    // 将每行数据封装为Object数组并存入列表
                    Object[] rowData = new Object[]{
                            resultSet.getString("consumption_id"),
                            resultSet.getDate("date_time"),
                            resultSet.getString("category"),
                            resultSet.getString("consumption_project"),
                            amount};
                    resultList.add(rowData);
                }

                // 按照日期（Object数组中的第一个元素，即索引为0的元素）降序排序
                Collections.sort(resultList, (o1, o2) -> {
                    java.sql.Date date1 = (java.sql.Date) o1[1];
                    java.sql.Date date2 = (java.sql.Date) o2[1];
                    return date2.compareTo(date1);
                });

                // 将排序后的数据添加到表格模型
                for (Object[] row : resultList) {
                    tableModel.addRow(row);
                }

                // 创建表格并设置模型，如果之前表格已存在则移除旧表格再添加新表格
                if (table!= null) {
                    scrollPane.setViewportView(null);
                }

                table = new JTable(tableModel);
                scrollPane.setViewportView(table);

                // 更新总计金额标签内容
                totalLabel.setText("总计金额：" + totalAmount);

            }

        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("数据库查询错误：" + e.getMessage());
        }
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> {
            Query q = new Query();
            q.QFace();
        });
    }
}